﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace TestePI2
{
    public partial class frmCadastro : Form
    {
        public string artista;
        public string caminhoNomeArquivoSelecionado;

        public frmCadastro()
        {
            InitializeComponent();
        }

        private void btnInserirImagem_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "JPG Files (*.jpg)|*.jpg| PNG Files (*.png)|*.png| GIF Files (*.gif)|*.gif";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                caminhoNomeArquivoSelecionado = ofd.FileName;

                if (caminhoNomeArquivoSelecionado.ToLower().EndsWith(".gif") ||
                    caminhoNomeArquivoSelecionado.ToLower().EndsWith(".jpg") ||
                    caminhoNomeArquivoSelecionado.ToLower().EndsWith(".png") )
                {
                    pbCadastroImagem.Image = new Bitmap(caminhoNomeArquivoSelecionado);

                    
                    try
                    {
                        byte[] imagemBytes;
                        FileStream fs = new FileStream(caminhoNomeArquivoSelecionado, FileMode.Open, FileAccess.Read);
                        BinaryReader br = new BinaryReader(fs);
                        SqlConnection Conn = new SqlConnection("Data Source=ULI-NOTE;Initial Catalog=PROJETO_MUSICA;Integrated Security=True");
                        SqlCommand cmd = new SqlCommand();

                        imagemBytes = br.ReadBytes(1200000);

                        Conn.Open();
                        cmd.Connection = Conn;
                        cmd.CommandText = @"insert into TB_ALBUM (IMAGEM_ALBUM) values (@imagem)";
                        cmd.Parameters.Add("@imagem", SqlDbType.Image, 1200000);
                        cmd.Parameters["@imagem"].Value = imagemBytes;
                        cmd.Prepare();
                        cmd.ExecuteNonQuery();
                        Conn.Close();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

        private void Cadastro_Load(object sender, EventArgs e)
        {
                try 
                {
                    SqlConnection conn = new SqlConnection("Data Source=ULI-NOTE;Initial Catalog=PROJETO_MUSICA;Integrated Security=True");
                    conn.Open();

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter("SELECT ID_ARTISTA, NM_ARTISTA FROM TB_ARTISTA ORDER BY NM_ARTISTA",conn);
                    cmbCadastraArtista.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
                
                    da.Fill(dt);
                    cmbCadastraArtista.DisplayMember = "NM_ARTISTA";
                    cmbCadastraArtista.ValueMember = "ID_ARTISTA";
                    cmbCadastraArtista.DataSource = dt;

                
                    conn.Close();
                } 
                catch(SqlException sqlExp) 
                {
                    MessageBox.Show(sqlExp.Message);
                }
        }

        private void cmbCadastraArtista_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn = new SqlConnection("Data Source=ULI-NOTE;Initial Catalog=PROJETO_MUSICA;Integrated Security=True");
                conn.Open();

                cmbCadastraArtista.ValueMember = "ID_ARTISTA";
                int codigo = Convert.ToInt32(cmbCadastraArtista.SelectedValue);
                cmbAlbum.ResetText();
                
                DataTable album = new DataTable();
                SqlDataAdapter adap = new SqlDataAdapter("SELECT NM_ALBUM FROM TB_ARTISTA_ALBUM    A INNER JOIN TB_ALBUM B ON A.ID_ALBUM = B.ID_ALBUM WHERE ID_ARTISTA = " + codigo, conn);
                adap.Fill(album);
                cmbAlbum.DisplayMember = "NM_ALBUM";
                cmbAlbum.DataSource = album;

                conn.Close();
            }
            catch (SqlException sqlExp)
            {
                MessageBox.Show(sqlExp.Message);
            }
        }

        private void cmbCadastraArtista_Leave(object sender, EventArgs e)
        {
            SqlDataReader dreader;
            SqlConnection Conn = new SqlConnection("Data Source=ULI-NOTE;Initial Catalog=PROJETO_MUSICA;Integrated Security=True");
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = Conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT NM_ARTISTA FROM TB_ARTISTA";
            
            Conn.Open();
            
            dreader = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            
            dt.Load(dreader);
            
            Conn.Close();

            AutoCompleteStringCollection local = new AutoCompleteStringCollection();
            if (dt.Rows.Count >= 0)
            {
                for (int count = 0; count < dt.Rows.Count; count++)
                {
                    local.Add(dt.Rows[count]["NM_ARTISTA"].ToString());
                }
            }

            if (! local.Contains(cmbCadastraArtista.Text))
            {
                SqlCommand inserir = new SqlCommand();
                inserir.Connection = Conn;
                inserir.CommandType = CommandType.Text;
                inserir.CommandText = "INSERT INTO TB_ARTISTA (NM_ARTISTA) VALUES ('" + cmbCadastraArtista.Text + "')";

                Conn.Open();
                inserir.ExecuteNonQuery();
                Conn.Close();

                MessageBox.Show("Artista inserido com sucesso!");
            }
            else
            {
                return;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Hellow World!");
        }
    }
}